Getting Started with xCL and xSQL

Xactly Connect offers two command languages: xCL and xSQL. xCL is designed to help you interact with Connect to gain an intuitive understanding of Connect assets. xSQL is a dialect of SQL (Structured Query Language) that provides the ability to query and manipulate data in a familiar and standard way.   

Xactly Connect also offers the Connect UI (User Interface) that you can use to interactively manipulate and view the status and state of the Connect environment. You can also use the Connect UI to submit commands and view results using a Console view. While xCL and xSQL are data oriented and offer a tabular data view of your environment, the Connect UI offers a graphical representation of the same environment.

Because xCL and xSQL are command languages other tools can interact with Connect using standard interfaces such as ODBC and JDBC. Any tool that uses one of these standard interfaces can submit commands to Connect and receive results.

Note: Since xCL and xSQL are command languages, external tools based on standard interfaces such as ODBC and JDBC can submit commands and receive results from Connect.

Reference our xCL Library

Reference our xSQL Library

Reference our Connect Domain Objects

xCL

xCL offers commands that manipulate and query the Connect environment and belong to one of six general categories: create, alter, drop, show, Incent, and other. Commands submitted to Connect, through ODBC, JDBC, REST API, or the Connect UI are run and the results are returned.

Note that the “other” category of commands include general purpose services such as ABORT INVOCATION or SET <variable>, among others.

Some xCL commands manipulate and query objects that are not explicitly created by the user but instead are created by the Connect system as byproducts of other activities.  For example, you cannot create the AUDIT asset using xCL but you can the asset using a SHOW command.

 

CREATE

Use the CREATE commands to create an instance of certain asset types. The commands create a persistent definition of a named object corresponding to the domain type. Each name is unique per object type, and a unique identifier is assigned to each object when it is created (that does not change as long as the object exists).

You can use the name or id in other xCL commands, depending on the command and context. Since each domain type has different characteristics, various versions of the CREATE command might have slightly different syntax.

Syntax

CREATE <domain type> <name> <definition>

Example

create pipeline p1;


create step s1 as (set var1 = 5);


create table delta.foobar (c1 string);

 

ALTER

Use the ALTER commands to change the characteristics of existing objects. You generally use the name to identify the object. Each time you alter an asset, a history of the previous state is maintained. You can view these previous asset versions using xCL, but it is more convenient to use the Connect UI.

Note that renaming an object is generally not allowed.

ALTER <domain type> <name> (<definition> | <verbs>)

alter pipeline p1 add step s1;

alter step s1 as (set var1 *= ‘2017’); 

 

DROP

Use the DROP commands to remove an object from the Connect environment. After running the command, the dropped object no longer exists in Connect. You generally use the name to identify the object.

Xactly Connect enforces referential integrity for certain assets; certain objects cannot be dropped from the Connect environment if they are referenced by another object that still exists. For example, you cannot drop a step if it is referenced within a pipeline. In this case, you need to drop the pipeline first.

DROP <domain type> <name>

drop email final_notice;

drop pipeline p1; 

 

SHOW

Use the SHOW commands to return sets of existing objects. You can also use the command to show information about the Connect system.

show pipelines;

show schedules;

show cursors;

show invocations;

 

INCENT

Incent command move data from the Connect staging areas and cause actions to occur in the Incent Application.

Incent upload order

Incent upload titles;

Incent 

 

OTHER

Use the OTHER commands to perform specific actions that affect user-defined assets or object types. For example, you can use OTHER commands to run pipelines, send emails, set variables, and perform other actions.

abort invocation <invocation_id>

set <variable_name> *= <expression>

invoke pipeline p1;

 

xSQL

xSQL is a dialect of the industry standard SQL (Structured Query Language). If you understand SQL then xSQL will be very familiar to you. xSQL supports the following SQL statements: select, insert, update, and delete. xSQL further provides extensions to SQL that makes it easy to manipulate tabular data using statements, functions, and procedures.

xSQL also includes multiple functions to complete specific tasks associated with Xactly applications. Finally, xSQL offers procedures to dynamically access data from external sources such as Salesforce and Workday.

SELECT

The SELECT statement returns a result set of records from one or more tables or procedures.  SELECT is the most commonly-used query command. The SELECT statement has the following optional clauses:

•FROM specifies tables or procedures that contain data
•WHERE specifies which rows to retrieve
•GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group
•HAVING selects among the groups defined by the GROUP BY clause
•ORDER BY specifies an order in which to return the rows
•LIMIT specifies the maximum number of rows to return
•OVER / WINDOW specifies how windowing aggregates will be applied
•UNION [ALL]

 

select * from xc_period;

select * from SFDC(CredentialName=’foobar’, SOQL=’select name from Opportunity’);

select LookupPeriodNameById(period_id) from xc_period;

 

 

INSERT

The INSERT statement adds one or more records to a single table or procedure within the Connect environment. The VALUES clause of the INSERT statement identifies the values and rows inserted.

You can also use the INSERT statement to retrieve data from tables or procedures, modify the data if necessary, and insert the data directly into the table. All this is possible using a single SQL statement that does not involve any intermediary processing in the client application.

In this case, a subselect is used instead of the VALUES clause. The subselect can contain joins, function calls, and can query the same table into which the data is inserted. Logically, the select is evaluated before the insert operation is started.

 

INSERT INTO <table / procedure> <source set>;

insert into staging.order_item select * from delta.final_order_item;

insert into myschema.mytable values (1), (2), (3);

 

UPDATE

The UPDATE statement modifies existing records in a table. You can use the WHERE clause with the UPDATE query to update selected rows; otherwise all rows are affected.  

UPDATE <table> SET <column = value> …   WHERE <expression>

 

DELETE

The DELETE statement deletes existing records from a table. You can use the WHERE clause with a DELETE query to delete selected rows; otherwise all records are deleted.

DELETE FROM <table> WHERE <expression>